The SQL UNION clause is used to combine the result table of two or more than two select statements by eliminating the duplicate data records or rows.
SQL UNIONS CLAUSE
We can only use the UNION operator between two SELECT statements if they follow the following criteria:
- Both the SELECT statements have the same number of Columns.
- The data type of both columns must be the same.
- The columns must be in the same order in both the SELECT statements.
Syntax
To use the UNION operator follow this Syntax:
SELECT column_name_1 FROM table1 UNION SELECT column_name_1 FROM table2;
Example For example, consider these two tables: Students
+------+--------+------+--------+-------+----------+ | id | name | age | grades | marks | Trade | +------+--------+------+--------+-------+----------+ | 1 | Luffy | 16 | A | 970 | Science | | 2 | Naruto | 18 | A | 960 | Humanity | | 3 | Zoro | 20 | A | 940 | Commerce | | 4 | Sanji | 21 | B | 899 | Humanity | | 5 | Nami | 17 | B | 896 | Science | | 6 | Robin | NULL | B | 860 | Humanity | | 7 | Goku | 27 | B | 860 | Humanity | +------+--------+------+--------+-------+----------+
Library
+---------+-------------------+------------+ | Book_ID | Book_Name | Student_ID | +---------+-------------------+------------+ | 1124 | One Hundred years | 1 | | 1104 | The Great Escape | 2 | | 1209 | Beloved | 6 | | 1111 | Hollow | 4 | | 2351 | Invisibal Man | 3 | | 1034 | A Passage | 2 | | 1211 | Hero | 6 | | 1188 | Your Name | 5 | | 1211 | Hero | 6 | | 1000 | My Hope | 8 | | 1000 | Go Away | 10 | +---------+-------------------+------------+
Query: Perform a Full Join operation with the help of the UNION operator:
SELECT students.id,students.name,library.Book_Name FROM students RIGHT JOIN library ON students.id = Library.student_Id UNION SELECT students.id,students.name,library.Book_Name FROM students LEFT JOIN library ON students.id = Library.student_Id;
Output
+------+--------+-------------------+ | id | name | Book_Name | +------+--------+-------------------+ | 1 | Luffy | One Hundred years | | 2 | Naruto | The Great Escape | | 2 | Naruto | A Passage | | 3 | Zoro | Invisibal Man | | 4 | Sanji | Hollow | | 5 | Nami | Your Name | | 6 | Robin | Beloved | | 6 | Robin | Hero | | NULL | NULL | My Hope | | NULL | NULL | Go Away | | 7 | Goku | NULL | +------+--------+-------------------+
Behind the Query
- Here we used the UNION operator between two select statements.
- The first select statement represents the RIGHT JOIN and the second represents the LEFT join.
- Both the select statements have the same number of columns, similar data type and the order columns in both select statements is also the same.
- If we look the individual result of RIGHT and LEFT join statements, they share some similar rows but the UNION operator eliminates the duplicate records and in the output, you can see that no two rows are similar.
UNION ALL
The UNION operator excludes the duplicates rows but if you want to include them too then you can use the UNION ALL operator, the syntax and working of UNION ALL is similar to UNION operator the only different is UNION ALL can include duplicates rows whereas UNION does not. Example For this example, we will use the above example and we replace the UNION operator with UNION ALL: Query
SELECT students.id,students.name,library.Book_Name FROM students RIGHT JOIN library ON students.id = Library.student_Id UNION ALL SELECT students.id,students.name,library.Book_Name FROM students LEFT JOIN library ON students.id = Library.student_Id;
Output
+------+--------+-------------------+ | id | name | Book_Name | +------+--------+-------------------+ | 1 | Luffy | One Hundred years | | 2 | Naruto | The Great Escape | | 2 | Naruto | A Passage | | 3 | Zoro | Invisible Man | | 4 | Sanji | Hollow | | 5 | Nami | Your Name | | 6 | Robin | Beloved | | 6 | Robin | Hero | | 6 | Robin | Hero | | NULL | NULL | My Hope | | NULL | NULL | Go Away | | 1 | Luffy | One Hundred years | | 2 | Naruto | The Great Escape | | 6 | Robin | Beloved | | 4 | Sanji | Hollow | | 3 | Zoro | Invisibal Man | | 2 | Naruto | A Passage | | 6 | Robin | Hero | | 5 | Nami | Your Name | | 6 | Robin | Hero | | 7 | Goku | NULL | +------+--------+-------------------+
Behind the Query
- Here the UNION ALL operator just combine the result table of RIGHT JOIN select statement table with LEFT JOIN select statement table.
- Here you can see that some rows have similar values.
Summary
- The UNION operator can combine the result of two or more than two tables and form a single temporary table.
- The two tables we are combining should have the similar number of selected columns, the data type of the first table selected columns must be similar to the data type of the second table selected columns, and the order of the first table selected columns must be similar to the order of the second table selected columns.
- To include the duplicates rows we can use the UNION ALL operator.
- SQL has two other operators INTERSECT and EXCEPT which are like UNION operator.
- The INTERSECT clause can also combine two SELECT statements and return a table with records which are similar in both the tables.
- his combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.
People are also reading: